package pro.survey.analyzer;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import jxl.*;
import jxl.read.biff.BiffException;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
import pro.survey.analyzer.data.Answers;
import pro.survey.analyzer.data.Imports;
import pro.survey.analyzer.data.Lectors;
import pro.survey.analyzer.data.Questions;

/**
 *
 * @author Adrian
 */
public class DataSheetLoader {

    public List<Lectors> lectors;
    private Workbook workbook;

    public DataSheetLoader() {
        lectors = new ArrayList<>();
    }

    public void loadFile(File file) {
        try {
            workbook = Workbook.getWorkbook(file);
        } catch (IOException | BiffException ex) {
            Logger.getLogger(GUIGenerator.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void loadDataFromFile() {
        Session session = HibernateUtil.getSession();
        Transaction transaction = session.beginTransaction();
        transaction.begin();
        try {
            Imports imp = new Imports();
            session.save(imp);
            ArrayList<Questions> questions = new ArrayList<>();
            for (int i = 3; i < workbook.getSheet(0).getColumns() - 2; i++) {
                if (!workbook.getSheet(0).getCell(i, 0).getContents().equals("")) {
                    Questions question = new Questions();
                    question.setText(workbook.getSheet(0).getCell(i, 0).getContents());
                    question.setImports(imp);
                    question.incAnswerCount();
                    questions.add(question);
                } else {
                    questions.get(questions.size() - 1).incAnswerCount();
                }
            }

            for (int i = 2; i < workbook.getSheet(0).getRows() - 2; i++) {
                Lectors lector = new Lectors();
                String[] name = workbook.getSheet(0).getCell(0, i).getContents().split(" ");
                lector.setName(name[0]);
                lector.setSurname(name[1]);
                lector.setLang(workbook.getSheet(0).getCell(1, i).getContents());
                lector.setImports(imp);
                session.save(lector);
                int y = 3;
                for (Questions question : questions) {
                    session.save(question);
                    for (int x = y; x < y + question.getAnswerCount(); x++) {
                        Answers a = new Answers();
                        a.setType(workbook.getSheet(0).getCell(x, 1).getContents());
                        a.setValue(workbook.getSheet(0).getCell(x, i).getContents());
                        a.setQuestions(question);
                        a.setLectors(lector);
                        a.setImports(imp);
                        session.save(a);
                    }
                    y += question.getAnswerCount();
                }
                lectors.add(lector);
            }
            transaction.commit();
        } catch (HibernateException | IndexOutOfBoundsException ex) {
            System.out.print(ex.getMessage());
            transaction.rollback();
        }
    }
}
